Sub UnpivotTransposeWithLinks()
    Dim srcSheet As Worksheet
    Dim tgtSheet As Worksheet
    Dim selRange As Range
    Dim r As Long, c As Long
    Dim yearRow As Long
    Dim tgtRow As Long, tgtCol As Long
    Dim wsName As String
    
    ' Set source sheet
    Set srcSheet = ThisWorkbook.Sheets("BS")
    wsName = srcSheet.Name
    
    ' Set selected range
    Set selRange = Application.Selection
    If selRange Is Nothing Then
        MsgBox "Please select a range with data first."
        Exit Sub
    End If
    
    ' Create/clear target sheet
    On Error Resume Next
    Set tgtSheet = ThisWorkbook.Sheets("Transposed")
    If tgtSheet Is Nothing Then
        Set tgtSheet = ThisWorkbook.Sheets.Add
        tgtSheet.Name = "Transposed"
    Else
        tgtSheet.Cells.Clear
    End If
    On Error GoTo 0
    
    ' Top-left header
    tgtSheet.Cells(1, 1).Value = "Year"
    
    ' Write particulars as headers in row 1 starting from column B
    For r = 2 To selRange.Rows.Count
        tgtSheet.Cells(1, r - 1 + 1).Value = selRange.Cells(r, 1).Value ' +1 since B is 2nd col
    Next r
    
    ' Write years in column A starting from row 2
    For c = 2 To selRange.Columns.Count
        tgtSheet.Cells(c - 1 + 1, 1).Value = selRange.Cells(1, c).Value ' +1 since row 2
        
        ' For each particular, write formula in appropriate column
        For r = 2 To selRange.Rows.Count
            tgtSheet.Cells(c - 1 + 1, r - 1 + 1).Formula = _
                "='" & wsName & "'!" & selRange.Cells(r, c).Address(False, False)
        Next r
    Next c
    
    MsgBox "Transposed data created with links to source sheet."
End Sub


